package db;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import twitter4j.auth.AccessToken;
import util.TimeCalculator;
import model.DailyMission;
import model.ProgramList;
import model.UserInfo;
import model.UserProjectList;

public class DbConn {
	private String dburl = "";
	private String dbuser = "";
	private String dbpass = "";

	public void setDbUrl(String url) {
		dburl = url;
	}

	public String getDbUrl() {
		return dburl;
	}

	public void setDbUser(String user) {
		dbuser = user;
	}

	public String getDbUser() {
		return dbuser;
	}

	public void setDbPass(String pass) {
		dbpass = pass;
	}

	public String getDbPass() {
		return dbpass;
	}

	/**
	 * Function for opening connection to the database
	 */
	public Connection getConnection() {

		Connection conn = null;
		try {
			conn = DriverManager.getConnection(getDbUrl(), getDbUser(),
					getDbPass());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * Function to close open connection to database
	 */
	public void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public boolean setAccessToken(String userid, String accessToken,
			String tokenSecret) {
		Connection conn = getConnection();
		try {
			String query = "UPDATE `painhealer`.`userlist` SET `accesstoken`=?, `accesstokensecret`=? WHERE `userId`=?";
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setString(1, accessToken);
			ps.setString(2, tokenSecret);
			ps.setString(3, userid);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			closeConnection(conn);
		}
		return true;
	}

	public String getAccessToken(String userid) {
		Connection conn = getConnection();
		String accesstoken = "";
		try {
			String query = "select accesstoken, accesstokensecret from painhealer.userlist where userId=?";
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setString(1, userid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String token = rs.getString("accesstoken");
				String secret = rs.getString("accesstokensecret");
				accesstoken = token + "&" + secret;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return accesstoken;
	}

	public String isExpiredProject(String userid) {
		Connection conn = getConnection();
		int totaldate = 0;
		String check = null;
		try {
			String query = "select start_date , totaldate from `painhealer`.`projectuserlist` where userid = '"
					+ userid + "'";
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery(query);
			if (rs.next()) {
				Timestamp start_date = rs.getTimestamp("start_date");
				totaldate = rs.getInt("totaldate");
				int dayoffset = TimeCalculator.getInstance()
						.getNextDayOfProject(start_date,
								new Timestamp(System.currentTimeMillis()));
				if (dayoffset > totaldate) {
					check = "Project Expired.";
				} else {
					check = "Project is now working.";
				}
			} else {
				check = "Project is not Found.";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return check;
	}

	public ArrayList<ProgramList> getProgramList() {

		Connection conn = getConnection();
		ArrayList<ProgramList> plist = new ArrayList<ProgramList>();
		String query = "select `programid`, `period`,`description`, `detaildescription` from `painhealer`.`programlist`";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				plist.add(new ProgramList(rs.getString(1), rs.getInt(2), rs
						.getString(3), rs.getString(4)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}

		return plist;
	}

	/**
	 * return ProgramId
	 * 
	 * @param userId
	 *            - userId
	 * @return Programid or null
	 */
	public String getProgramIdFromProject(String userId) {
		Connection conn = getConnection();
		String result = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String query = "select programid from painhealer.projectuserlist where userid ='"
				+ userId + "'";
		try {
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				result = rs.getString("programid");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("program id : " + result);
		return result;

	}

	/**
	 * Check User Duplicate
	 * 
	 * @param userId
	 * @return not duplicate - false, duplicate - true
	 */
	public boolean checkUser(String userId) {
		Connection conn = getConnection();
		boolean result = true;
		String query = "select userId from `painhealer`.`userlist` where userId = '"
				+ userId + "'";
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next())
				result = false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		closeConnection(conn);
		return result;
	}

	/**
	 * Login User
	 * 
	 * @param userid
	 * @param userpw
	 * @return login ok - true , login fail - false
	 */
	public boolean loginUser(String userid, String userpw) {
		Connection conn = getConnection();
		boolean result = false;
		if (conn != null) {
			ResultSet rs = null;
			PreparedStatement ps = null;
			try {
				String sqlQuery = "SELECT userId FROM `painhealer`.`userlist` WHERE userId='"
						+ userid + "' and password='" + userpw + "'";
				ps = conn.prepareStatement(sqlQuery);
				rs = ps.executeQuery();
				while (rs.next()) {
					result = true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		closeConnection(conn);
		return result;
	}

	/**
	 * update Project user into database
	 * @param programId
	 * @param userId
	 * @param now - now weight
	 * @param goal - goal weight
	 * @param totalDate
	 */
	public void setProjectUserList(String programId, String userId, int now,
			int goal, int totalDate) {
		String query = "INSERT INTO `painhealer`.`projectuserlist` (`programid`, `userid`, `init_weight`, `dest_weight` , `totaldate`) VALUES (?, ?, ?, ?, ?)";
		PreparedStatement ps = null;
		Connection conn = getConnection();
		try {
			ps = conn.prepareStatement(query);
			ps.setString(1, programId);
			ps.setString(2, userId);
			ps.setInt(3, now);
			ps.setInt(4, goal);
			ps.setInt(5, totalDate);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
	}

	/**
	 * HashMap
	 * 
	 */
	public HashMap<String, Object> getDailyInitData(String userId) {
		Connection conn = getConnection();
		HashMap<String, Object> data = new HashMap<String, Object>();
		boolean isduplicate = false;
		String programid = getProgramIdFromProject(userId);
		if (programid == null) {
			data.put("error", "user is not start project!");
			return data;
		}
		int dayoffset = 0;
		int totaldate = 0;
		try {
			PreparedStatement ps1 = conn
					.prepareStatement("select `start_date` , `totaldate` from `painhealer`.`projectuserlist` where `userid`='"
							+ userId + "'");
			ResultSet rs1 = ps1.executeQuery();
			if (!rs1.next()) {
				data.put("error", "noquery");
				return data;
			} else {
				Timestamp startdate = rs1.getTimestamp("start_date");
				totaldate = rs1.getInt("totaldate");
				Timestamp currentdate = new Timestamp(
						System.currentTimeMillis());
				dayoffset = (int) ((currentdate.getTime() - startdate.getTime())
						/ (1000 * 24 * 60 * 60) + 1);
				if (dayoffset > totaldate) {
					data.put("error",
							"project is expired. giveup and make new project");
					return data;
				} else {
					data.put("dayoffset", dayoffset);
					data.put("totaldate", totaldate);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		}

		try {
			PreparedStatement ps = conn
					.prepareStatement("select * from `painhealer`.`currentproject` where `userid` = ? and `day`= ? and `programid` = ?");
			ps.setString(1, userId);
			ps.setInt(2, dayoffset);
			ps.setString(3, programid);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
				isduplicate = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (isduplicate) {
			data.put("error", "error.");
			return data;
		}

		try {
			System.out.println("programid:" + programid + " dayoffset:"
					+ dayoffset + " totaldate:" + totaldate + " userid:"
					+ userId);

			// programid , dayoffset , totaldate , userid 瑜�媛��怨�foodset 媛�졇�ㅺ린
			PreparedStatement ps1 = conn
					.prepareStatement("select foodList1 , foodList2, foodList3 from painhealer.foodsetlist where foodSetId = (select foodset from painhealer.program where program.programid = ? and program.daycount = ?);");
			ps1.setString(1, programid);
			ps1.setInt(2, dayoffset);
			ResultSet rs1 = ps1.executeQuery();
			while (rs1.next()) {
				System.out.println(rs1.toString());
				for (int i = 1; i < 4; i++) {
					System.out.println(rs1.getString(i));
					String foodlistid = rs1.getString(i);
					PreparedStatement ps2 = conn
							.prepareStatement("select * from painhealer.foodlisttable where foodId = ?");
					ps2.setString(1, foodlistid);
					ResultSet rs2 = ps2.executeQuery();
					while (rs2.next()) {
						data.put("foodId" + i, rs2.getString("foodId"));
						data.put("foodName" + i, rs2.getString("foodName"));
					}
				}
			}
			// programid , dayoffset , totaldate , userid 瑜�媛��怨�exerset 媛�졇�ㅺ린

			PreparedStatement ps2 = conn
					.prepareStatement("select exerlist1 , exerlist2, exerlist3 from painhealer.exersetlist where exerSetId = (select exerset from painhealer.program where program.programid = ? and program.daycount = ?);");
			ps2.setString(1, programid);
			ps2.setInt(2, dayoffset);
			ResultSet rs2 = ps2.executeQuery();
			while (rs2.next()) {
				for (int i = 1; i < 4; i++) {
					String exerlistid = rs2.getString(i);
					PreparedStatement ps3 = conn
							.prepareStatement("select * from painhealer.exerlisttable where exerId = ?");
					ps3.setString(1, exerlistid);
					ResultSet rs3 = ps3.executeQuery();
					while (rs3.next()) {
						data.put("exerId" + i, rs3.getString("exerId"));
						data.put("exerName" + i, rs3.getString("exerName"));
					}
				}
			}
			data.put("programid", programid);
			data.put("dayoffset", dayoffset);
			data.put("totaldate", totaldate);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return data;
	}

	/**
	 * �닿굔 �덉쟾���덈뜕 �꾨줈�앺듃��以묒뿉 �ъ슜�먭� 蹂닿퀬�띠� �꾨줈�앺듃瑜�怨⑤씪��洹몄뿉 愿�븳
	 * �뺣낫瑜�媛�졇�ㅻ뒗寃껋엫 洹몃윭�덇퉴 oldprojectlist��projectid 媛숈쑝硫�day���곕씪
	 * �쒖감�곸쑝濡�媛�졇�ㅺ린 留뚯빟 isOldProject媛�true �대㈃ oldprojectlist �먯꽌 媛�졇�ㅺ퀬 false
	 * �대㈃ currentProject�먯꽌 媛�졇�ㅻ㈃��JSONArray�� JSONObject瑜�add��put
	 * �쒗궎硫��섎뒗��JSONObject���ㅼ쓬怨�媛숈씠 ��옣 programId day todayWeight comment
	 * food1Name (food1 �대쫫) isFood1 (food1 t/f) food2Name isFood2 food3Name
	 * isFood3 exer1Name isExer1 exer2Name isExer2 exer3Name isExer3
	 * 
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public JSONArray getDataFromCurrentProject(String userId, String projectid) {
		JSONArray arr = new JSONArray();
		Connection conn = getConnection();
		try {
			// PreparedStatement ps =
			// conn.prepareStatement("drop view painhealer.p");
			// ps.executeUpdate();

			PreparedStatement ps = conn
					.prepareStatement("create view painhealer.p (foodset, exerset , day) as "
							+ "select foodset , exerset, day "
							+ "from painhealer.currentproject join painhealer.program "
							+ "where userid = '"
							+ userId
							+ "' and currentproject.programid = program.programid "
							+ "and currentproject.day = program.daycount");
			ps.executeUpdate();

			ResultSet rs = ps
					.executeQuery("select * "
							+ "from painhealer.p join painhealer.foodsetlist , painhealer.exersetlist "
							+ "where p.foodset = foodsetlist.foodsetId and p.exerset = exersetlist.exersetId");
			while (rs.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("foodlist1", rs.getString("foodList1"));
				map.put("foodlist2", rs.getString("foodList2"));
				map.put("foodlist3", rs.getString("foodList3"));
				map.put("exerlist1", rs.getString("exerlist1"));
				map.put("exerlist2", rs.getString("exerlist2"));
				map.put("exerlist3", rs.getString("exerlist3"));

				PreparedStatement ps2 = conn
						.prepareStatement("select * "
								+ "from painhealer.foodlisttable ,painhealer.exerlisttable "
								+ "where (foodlisttable.foodId = ? and exerlisttable.exerId = ?) "
								+ "or (foodlisttable.foodId = ? and exerlisttable.exerId = ?) "
								+ "or (foodlisttable.foodId = ? and exerlisttable.exerId = ?)");
				ps2.setString(1, map.get("foodlist1"));
				ps2.setString(2, map.get("exerlist1"));
				ps2.setString(3, map.get("foodlist2"));
				ps2.setString(4, map.get("exerlist2"));
				ps2.setString(5, map.get("foodlist3"));
				ps2.setString(6, map.get("exerlist3"));
				ResultSet rs2 = ps2.executeQuery();
				JSONObject json = new JSONObject();
				int i = 1;
				while (rs2.next()) {
					json.put("foodid" + i, rs2.getString("foodId"));
					json.put("foodname" + i, rs2.getString("foodName"));
					json.put("exerid" + i, rs2.getString("exerId"));
					json.put("exername" + i, rs2.getString("exerName"));
					i++;
				}
				PreparedStatement ps3 = conn.prepareStatement("select * "
						+ "from painhealer.currentproject "
						+ "where userid = ? and day = ?");
				ps3.setString(1, userId);
				ps3.setInt(2, rs.getInt("day"));
				ResultSet rs3 = ps3.executeQuery();
				while (rs3.next()) {
					json.put("isfood1", rs3.getString("food1"));
					json.put("isfood2", rs3.getString("food2"));
					json.put("isfood3", rs3.getString("food3"));
					json.put("isexer1", rs3.getString("exer1"));
					json.put("isexer2", rs3.getString("exer2"));
					json.put("isexer3", rs3.getString("exer3"));
					json.put("comment", rs3.getString("comment"));
					json.put("todayweight", rs3.getString("todayweight"));
					json.put("day", rs3.getString("day"));
				}
				arr.add(json);
			}
			PreparedStatement p = conn
					.prepareStatement("drop view painhealer.p");
			p.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return arr;
	}

	@SuppressWarnings("unchecked")
	public JSONArray getDataFromOldProject(String userId, String programid,
			int completecnt) {
		JSONArray arr = new JSONArray();
		Connection conn = getConnection();
		HashMap<String, String> map = new HashMap<String, String>();

		try {
			// View 留뚮뱾湲�
			PreparedStatement ps = conn
					.prepareStatement("create view painhealer.k ( foodset , exerset , day , programid ,completecnt) "
							+ "as select foodset , exerset , day , oldproject.programid , oldproject.completecount "
							+ "from painhealer.oldproject join painhealer.program "
							+ "where oldproject.programid = '"
							+ programid
							+ "' and program.programid = '"
							+ programid
							+ "' and oldproject.`day` = program.daycount and oldproject.userid = '"
							+ userId
							+ "' and oldproject.completecount = '"
							+ completecnt + "'");
			ps.executeUpdate();
			ps.executeUpdate("create view painhealer.t ( foodset ,foodlist1 ,foodlist2 ,foodlist3 ,exerset , exerlist1,exerlist2,exerlist3 , programid , day, completecnt) as select foodset, foodlist1, foodlist2 , foodlist3 , exerset , exerlist1, exerlist2 , exerlist3 , programid , day, completecnt from painhealer.k , painhealer.foodsetlist, painhealer.exersetlist where k.foodset = foodsetlist.foodSetId or k.exerset = exersetlist.exerSetId group by k.day");

			ResultSet rs = ps.executeQuery("select * from painhealer.t");
			while (rs.next()) {
				map.put("foodlist1", rs.getString("foodlist1"));
				map.put("foodlist2", rs.getString("foodlist2"));
				map.put("foodlist3", rs.getString("foodlist3"));
				map.put("exerlist1", rs.getString("exerlist1"));
				map.put("exerlist2", rs.getString("exerlist2"));
				map.put("exerlist3", rs.getString("exerlist3"));

				PreparedStatement ps2 = conn
						.prepareStatement("select * "
								+ "from painhealer.foodlisttable ,painhealer.exerlisttable "
								+ "where (foodlisttable.foodId = ? and exerlisttable.exerId = ?) "
								+ "or (foodlisttable.foodId = ? and exerlisttable.exerId = ?) "
								+ "or (foodlisttable.foodId = ? and exerlisttable.exerId = ?)");
				ps2.setString(1, map.get("foodlist1"));
				ps2.setString(2, map.get("exerlist1"));
				ps2.setString(3, map.get("foodlist2"));
				ps2.setString(4, map.get("exerlist2"));
				ps2.setString(5, map.get("foodlist3"));
				ps2.setString(6, map.get("exerlist3"));
				ResultSet rs2 = ps2.executeQuery();
				JSONObject json = new JSONObject();
				int i = 1;
				while (rs2.next()) {
					json.put("foodid" + i, rs2.getString("foodId"));
					json.put("foodname" + i, rs2.getString("foodName"));
					json.put("exerid" + i, rs2.getString("exerId"));
					json.put("exername" + i, rs2.getString("exerName"));
					i++;
				}
				PreparedStatement ps3 = conn
						.prepareStatement("select * from painhealer.oldproject where userid = ? and programid = ? and completecount = ? and day = ?");
				ps3.setString(1, userId);
				ps3.setString(2, programid);
				ps3.setInt(3, rs.getInt("completecnt"));
				ps3.setInt(4, rs.getInt("day"));
				ResultSet rs3 = ps3.executeQuery();
				while (rs3.next()) {
					json.put("isfood1", rs3.getString("food1"));
					json.put("isfood2", rs3.getString("food2"));
					json.put("isfood3", rs3.getString("food3"));
					json.put("isexer1", rs3.getString("exer1"));
					json.put("isexer2", rs3.getString("exer2"));
					json.put("isexer3", rs3.getString("exer3"));
					json.put("comment", rs3.getString("comment"));
					json.put("todayweight", rs3.getString("todayweight"));
					json.put("day", rs3.getString("day"));
				}
				arr.add(json);
			}
			PreparedStatement p = conn
					.prepareStatement("drop view painhealer.k");
			p.executeUpdate();
			p.executeUpdate("drop view painhealer.t");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return arr;
	}

	/**
	 * �닿굔 getOldProjectList 瑜�媛�졇�ㅺ린 �꾩뿉 洹�紐⑸줉�ㅼ쓣 媛�졇��빞 �섎뒗��UserProjectList
	 * model���쒕쾲 蹂대㈃ �댄빐媛�媛����덉쓣 ���곗씠�고삎��留욎텛�쒖삤
	 */
	public ArrayList<UserProjectList> getUserProjectList(String userId) {
		Connection conn = getConnection();

		ArrayList<UserProjectList> projectList = new ArrayList<UserProjectList>();

		// Old Program�쇰줈遺�꽣
		try {
			String query = "SELECT completecount,userid,`oldproject`.programid,description from `painhealer`.`oldproject`,`painhealer`.`programlist` where userid ='"
					+ userId
					+ "' and oldproject.programid = programlist.programid group by completecount";
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				UserProjectList userproject = new UserProjectList();
				userproject.setCompleteCnt(rs.getInt("completecount"));
				userproject.setProgramId(rs.getString("programid"));
				userproject.setProgramName(rs.getString("description"));
				userproject.setEnd(true);
				projectList.add(userproject);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		}

		// Current Project濡쒕���
		try {
			String query = "select userid,`currentproject`.programid , description from `painhealer`.`currentproject` , `painhealer`.`programlist` where userid = '"
					+ userId
					+ "' and currentproject.programid =programlist.programid group by userid";
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				UserProjectList userproject = new UserProjectList();
				userproject.setEnd(false);
				userproject.setProgramId(rs.getString("programid"));
				userproject.setProgramName(rs.getString("description"));
				projectList.add(userproject);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return projectList;
	}

	/**
	 * �닿굔 �꾨줈洹몃옩 由ъ뒪�몄뿉���꾨줈洹몃옩 �꾩씠�붾줈 洹��꾨줈洹몃옩��珥��좎쭨瑜�媛�졇�ㅻ뒗嫄곗엫
	 * �꾨줈�앺듃���꾨줈洹몃옩�대옉 �룰컝由щ뒗��洹몃깷 �곕룄濡���
	 * 
	 * @param programId
	 * @return
	 */
	public int getTotalDateFromProgramList(String programId) {
		Connection conn = getConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;
		int period = 0;
		String query = "select `period` from `painhealer`.`programlist` where `programid`='"
				+ programId + "'";
		try {
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				period = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return period;
	}

	/**
	 * �좎� �깅줉�섎뒗 �⑥닔
	 * 
	 * @param userinfo
	 * @return
	 */
	public Boolean registerUser(UserInfo userinfo) {
		Boolean result = true;
		Connection conn = getConnection();
		try {
			PreparedStatement ps = conn
					.prepareStatement("INSERT INTO `painhealer`.`userlist` (`userId`, `name`, `password`, `state`, `email`, `height`) VALUES (?, ?, ?, ?, ?, ?)");
			ps.setString(1, userinfo.getUserId());
			ps.setString(2, userinfo.getUserName());
			ps.setString(3, userinfo.getUserPw());
			ps.setString(4, userinfo.getState());
			ps.setString(5, userinfo.getEmail());
			ps.setString(6, userinfo.getHeight());
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		} finally {
			if (conn != null)
				closeConnection(conn);
		}
		return result;
	}

	public int getNextDayInCurrentProject(String userid) {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int dayofproject = 0;
		String query = "select start_date from `painhealer`.`projectuserlist` where userid = '"
				+ userid + "'";
		try {
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			Timestamp startdate = null;
			Timestamp todate = new Timestamp(System.currentTimeMillis());
			while (rs.next()) {
				startdate = rs.getTimestamp("start_date");
			}
			if (startdate != null && todate != null) {
				dayofproject = TimeCalculator.getInstance()
						.getNextDayOfProject(startdate, todate);
			} else if (startdate == null) {
				System.out.println("no user in projectuser where userid ="
						+ userid);
				return -1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return dayofproject;
	}

	public boolean setCurrentProject(DailyMission data) {
		data.setDay(getNextDayInCurrentProject(data.getUserId()));
		Connection conn = getConnection();
		try {
			PreparedStatement ps = conn
					.prepareStatement("INSERT INTO `painhealer`.`currentproject` (`programid`, `userid`, `day`, `food1`, `food2`, `food3`, `exer1`, `exer2`, `exer3`, `todayweight`, `comment`) VALUES (? , ? , ? , ? , ? , ? , ? , ? , ? , ?,?)");
			if (data.validation()) {
				ps.setString(1, data.getProgramId());
				ps.setString(2, data.getUserId());
				ps.setInt(3, data.getDay());
				ps.setString(4, data.getIsCheckFood1());
				ps.setString(5, data.getIsCheckFood2());
				ps.setString(6, data.getIsCheckFood3());
				ps.setString(7, data.getIsCheckExer1());
				ps.setString(8, data.getIsCheckExer2());
				ps.setString(9, data.getIsCheckExer3());
				ps.setInt(10, data.getTodayWeight());
				ps.setString(11, data.getComment());
			}
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return true;
	}

	public void giveUpCurrentProject(String userid) {
		Connection conn = getConnection();
		int nextcompletecnt = 1;
		try {
			PreparedStatement ps = conn
					.prepareStatement("select max(completecount) from painhealer.oldproject where userid=?");
			ps.setString(1, userid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				nextcompletecnt = rs.getInt(1);
				nextcompletecnt++;
			}
			PreparedStatement ps2 = conn
					.prepareStatement("select userid , programid , day , todayweight , comment , food1, food2, food3 ,exer1, exer2, exer3 from painhealer.currentproject where userid=?");
			ps2.setString(1, userid);
			ResultSet rs2 = ps2.executeQuery();
			while (rs2.next()) {
				PreparedStatement ps3 = conn
						.prepareStatement("INSERT INTO `painhealer`.`oldproject` (`completecount`, `userid`, `programid`, `day`, `todayweight`, `comment`, `food1`, `food2`, `food3`, `exer1`, `exer2`, `exer3`) VALUES (? ,? ,? , ? ,?,?,?,?,?,?,?,?)");
				ps3.setInt(1, nextcompletecnt);
				ps3.setString(2, rs2.getString("userid"));
				ps3.setString(3, rs2.getString("programid"));
				ps3.setInt(4, rs2.getInt("day"));
				ps3.setInt(5, rs2.getInt("todayweight"));
				ps3.setString(6, rs2.getString("comment"));
				ps3.setString(7, rs2.getString("food1"));
				ps3.setString(8, rs2.getString("food2"));
				ps3.setString(9, rs2.getString("food3"));
				ps3.setString(10, rs2.getString("exer1"));
				ps3.setString(11, rs2.getString("exer2"));
				ps3.setString(12, rs2.getString("exer3"));
				ps3.executeUpdate();
			}
			PreparedStatement ps3 = conn
					.prepareStatement("DELETE FROM `painhealer`.`currentproject` WHERE userid=?");
			ps3.setString(1, userid);
			ps3.executeUpdate();
			PreparedStatement ps4 = conn
					.prepareStatement("DELETE FROM `painhealer`.`projectuserlist` WHERE userid=?");
			ps4.setString(1, userid);
			ps4.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
	}

	public boolean isFinishedProject(String userid) {
		Connection conn = getConnection();
		boolean isfinished = false;
		try {
			PreparedStatement ps = conn
					.prepareStatement("select max(day) , totaldate from painhealer.currentproject join painhealer.projectuserlist where currentproject.userid = projectuserlist.userid and currentproject.userid=? group by currentproject.userid");
			ps.setString(1, userid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int day = rs.getInt(1);
				int totalday = rs.getInt(2);
				if (day == totalday)
					isfinished = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return isfinished;
	}

	@SuppressWarnings("unchecked")
	public JSONArray getProjectStatus(String userid) {
		Connection conn = getConnection();
		JSONArray arr = new JSONArray();
		JSONObject json = new JSONObject();
		try {
			json.put("status", isExpiredProject(userid));
			PreparedStatement ps = conn
					.prepareStatement("select init_weight , dest_weight , start_date , totaldate , description from painhealer.projectuserlist join painhealer.programlist where projectuserlist.programid = programlist.programid and userid = ?");
			ps.setString(1, userid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				json.put("description", rs.getString("description"));
				json.put("initweight", rs.getInt("init_weight"));
				json.put("destweight", rs.getInt("dest_weight"));
				// json.put("startdate",
				// rs.getTimestamp("start_date").toString());
				json.put("totaldate", rs.getInt("totaldate"));
			}
			ps = conn
					.prepareStatement("select count(*) from painhealer.currentproject where userid=?");
			ps.setString(1, userid);
			rs = ps.executeQuery();
			while (rs.next()) {
				json.put("daynum", rs.getInt(1));
			}
			arr.add(json);

			PreparedStatement ps2 = conn
					.prepareStatement("select day , todayweight , write_time from painhealer.currentproject where userid = ?");
			ps2.setString(1, userid);
			ResultSet rs2 = ps2.executeQuery();
			while (rs2.next()) {
				JSONObject json2 = new JSONObject();
				json2.put("day", rs2.getInt("day"));
				json2.put("todayweight", rs2.getInt("todayweight"));
				// json2.put("writetime",
				// rs2.getTimestamp("write_time").toString());
				arr.add(json2);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return arr;
	}
}
